import os
from flask import Flask, jsonify, make_response, send_file, render_template
from flask import request
import requests
from flask_cors import *
import json
import re
import time
import pymysql
import pandas as pd

user_type_dict = {-1: '无认证', 0: '个人账户', 3: '非个人用户'}  # 个人：个人账户仍保留橙色黄色标识，非个人用户(机构、企业、媒体等)将采用全新蓝色标识。

app = Flask(__name__)
CORS(app, spports_credentials=True)

conn = pymysql.connect(host='47.104.196.155', user="Sunzl", passwd="123456", port=3306,
                       db="Scary")
cur = conn.cursor()
print("数据库连接成功")


def ScaryData():
    print("正在爬取")
    sql_keyword = "select keyword from keyword where name='search'"

    cur.execute(sql_keyword)
    row = cur.fetchall()
    keyword = row[0][0]
    print("开始爬取"+keyword)
    for i in range(1,10000):
        try:
            print("正在爬取第" + str(i) + "页")
            get_one_page(i,keyword)
        except:
            print("内容含有未知字符或文本太长，插入数据库失败")
            continue
        time.sleep(2)
        sql_over = "select tip from cmd where name='overRequest'"
        print(sql_over)
        cur.execute(sql_over)
        print("overRequest SECDEED!")
        row = cur.fetchall()
        overRequest = row[0][0]
        print("overRequest:--------------")
        print(overRequest)
        if overRequest == 1:
            sql_start = "update cmd set tip=0 where name='overRequest'"
            cur.execute(sql_start)
            conn.commit()
            print('ScarySinaOver')
            return 'ScarySinaOver'



def wait():
    sql_stop = "select tip from cmd where name='stopRequest'"
    sql_len = "select * from ViboTemp"
    sql_update = "update cmd set tip=0 where name='stopRequest'"
    cur.execute(sql_stop)
    row = cur.fetchall()
    stopRequest = row[0][0]
    while stopRequest == 1:
        Len = cur.execute(sql_len)
        print("LEN")
        print(Len)
        if Len <= 5:
            cur.execute(sql_update)
            stopRequest = 0
            conn.commit()
        time.sleep(1)
        print("Scary Already Stop!")


def get_one_page(i,keyword):
    print("开始响应")
    params = {
        'containerid': f'100103type=1&q={keyword}',
        'page_type': 'searchall',
        'page': str(i)
    }
    url = 'https://m.weibo.cn/api/container/getIndex?'  # 请求api
    response = requests.get(url, params=params).text
    time.sleep(3)
    id_ls = re.findall('"id":"(.{16}?)",', response, re.DOTALL)
    detail_url = ['https://m.weibo.cn/detail/' + i for i in id_ls]
    for i in detail_url:
        print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time())))
        time.sleep(2)
        sql_stop = "select tip from cmd where name='stopRequest'"
        sql_len = "select * from ViboTemp"
        sql_update = "update cmd set tip=0 where name='stopRequest'"
        cur.execute(sql_stop)
        row = cur.fetchall()
        stopRequest = row[0][0]
        while stopRequest == 1:
            sql_over = "select tip from cmd where name='overRequest'"
            cur.execute(sql_over)
            row = cur.fetchall()
            overRequest = row[0][0]
            if overRequest == 1:
                print('ScarySinaOver')
                return 'ScarySinaOver'
            Len = cur.execute(sql_len)
            print("LEN")
            print(Len)
            if Len <= 5:
                cur.execute(sql_update)
                stopRequest = 0
                conn.commit()
            time.sleep(1)
            print("Scary Already Stop!")
        sql_over = "select tip from cmd where name='overRequest'"
        cur.execute(sql_over)
        row = cur.fetchall()
        overRequest = row[0][0]
        sql_len = "select * from ViboTemp"
        row = cur.execute(sql_len)
        print("")
        print(row)
        sql_update = "update cmd set tip=1 where name = 'stopRequest'"
        if row >= 500:
            cur.execute(sql_update)
            conn.commit()
        if overRequest == 1:
            print('ScarySinaOver')
            return 'ScarySinaOver'
        response = requests.get(i).text
        print("响应成功")
        # print(response)
        data = re.findall("var \$render_data = \[({.*})]\[0]", response, re.DOTALL)[0]
        data = json.loads(data)['status']
        # 发文时间
        created_at_time = data['created_at']
        timeArray = time.strptime(created_at_time, "%a %b %d %H:%M:%S +0800 %Y")
        created_at_time = time.strftime("%Y-%m-%d %H:%M:%S", timeArray)
        # 文章id
        log_id = data['id']
        # 文章内容
        log_text = data['text']
        log_text = re.sub('<.*?>', '', log_text)
        # 发文设备
        source = data["source"]
        # 转发数"reports_count": 1376,
        reports_count = data['reposts_count']
        # 评论数"comments_count": 10244,
        comments_count = data['comments_count']
        # 点赞数"attitudes_count": 326440
        attitudes_count = data['attitudes_count']
        # 用户id-->"id": 1951123110,
        user_id = data['user']['id']
        # 用户昵称
        user_screen_name = data['user']['screen_name']
        # 用户验证类型-->"verified_type": 3,
        user_type = data['user']['verified_type']

        if user_type not in user_type_dict:
            continue
        user_type = user_type_dict[user_type]
        Time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))
        one_piece_data = (user_id, created_at_time, i, log_text, source, reports_count,
                          comments_count, attitudes_count, user_screen_name, user_type, Time, keyword)
        print(one_piece_data)
        # insert_sql = "insert into Vibo(user_id,created_at_time,title_address,title_content,phone_source,forward_count ,comment_count,attitudes_count,user_screen_name,user_type,scary_date,keyword )VALUES( '{0}', '{1}','{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}','{10}','{11}')".format(
        #     user_id, created_at_time, i, log_text, source, reports_count, comments_count, attitudes_count,
        #     user_screen_name, user_type, Time, keyword)
        cur.callproc('add_data',(user_id, created_at_time, i, log_text, source, reports_count, comments_count, attitudes_count,
            user_screen_name, user_type, Time, keyword))
        conn.commit()
        cur.callproc('add_temp_data',(user_id, created_at_time, i, log_text, source, reports_count, comments_count, attitudes_count,
                      user_screen_name, user_type, Time, keyword))
        conn.commit()
        # insert_sqlT = "insert into ViboTemp(user_id,created_at_time,title_address,title_content,phone_source,forward_count ,comment_count,attitudes_count,user_screen_name,user_type,scary_date,keyword )VALUES( '{0}', '{1}','{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}','{10}','{11}')".format(
        #     user_id, created_at_time, i, log_text, source, reports_count, comments_count, attitudes_count,
        #     user_screen_name, user_type, Time, keyword)
        print(Time)
        print("开始装入数据库")

        print("insert succeed!")
        print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time())))


@app.route('/', methods=['GET', 'POST'])
def home():
    return render_template('index.html')# 返回home.html模板


@app.route('/login', methods=['GET', 'POST'])
def Login():
    Time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))

    if request.json['username'] == 'Lening' and request.json['password'] == 'admin':
        print(Time + "Login SUCCEED")
        return {'isPermit': True}
    else:
        print()
        print(Time + "Login FAIL")
        return {'isPermit': False}


@app.route('/ScarySina', methods=['GET', 'POST'])
def ScarySina():
    keyword = request.json['keyword']
    set_start_key = "update textTemp set state='{0}'where name='keyword'".format(keyword)
    set_time = "update textTemp set state='{0}'where name='time'".format(time.time())
    sql = "update keyword set keyword='{0}'where name='search'".format(keyword)
    cur.execute(sql)
    print("update right")
    conn.commit()
    sql_check = "update cmd set tip=0 where name='overRequest'"
    cur.execute(sql_check)
    conn.commit()
    sql_start = "select tip from cmd where name='startRequest'"
    set_start = "update cmd set tip=1 where name='startRequest'"
    cur.execute(sql_start)
    row = cur.fetchall()
    if row[0][0] == 0:
        cur.execute(set_start)
        conn.commit()
        cur.execute(set_time)
        conn.commit()
        cur.execute(set_start_key)
        conn.commit()
        print("开始爬取话题：" + keyword)
        ScaryData()
        return '开始爬取'
    else:
        return '正在爬取中，请先结束当前任务'


@app.route("/over", methods=['GET', 'POST'])
def me_api():
    sql ="update cmd set tip = 1 where name = 'overRequest'"
    sql_start = "update cmd set tip=0 where name='startRequest'"
    sql_delete ="delete from ViboTemp"
    set_over = "update textTemp set state='true' where name='reset'"
    sql_count = "update dateCount set count=0 where name='tempDate'"
    cur.execute(sql)
    conn.commit()
    cur.execute(set_over)
    conn.commit()
    cur.execute(sql_start)
    conn.commit()
    print("OVER SET!")
    sql_select = "select tip from cmd where name='overRequest'"
    cur.execute(sql_select)
    row = cur.fetchall()
    tip = row[0][0]
    print("tip=:!!!!!!!!!!")
    print(tip)
    cur.execute(sql_delete)
    conn.commit()
    cur.execute(sql_count)
    conn.commit()
    return 'Scary over!'


@app.route("/getOne", methods=['GET', 'POST'])
def getOne():
    sql = "select * from ViboTemp"
    sql_count = "select count from dateCount where name='tempDate'"
    cur.execute(sql_count)
    fow = cur.fetchall()
    print(fow)
    print(fow[0])
    print(fow[0][0])
    row = cur.execute(sql)
    if row == 0:
        return 'NULL'
    else:
        select_sql = "select * from ViboTemp limit 1"
        delete_sql = "delete from ViboTemp limit 1"
        cur.execute(select_sql)
        row = cur.fetchall()
        for i in row:
            one_piece_data = (i[0],i[1] ,i[2], i[3], i[4], i[5], i[6],i[7], i[8], i[9], i[10], i[11],fow[0][0])
            column_name = ('user_id', 'created_at_time', 'title_address', 'title_content', 'phone_source', 'forward_count',
                           'comment_count', 'attitudes_count', 'user_screen_name', 'user_type', 'scary_date', 'keyword','count')
            data = dict(zip(column_name, one_piece_data))
        cur.execute(delete_sql)
        conn.commit()
        return data


@app.route("/queryKeyword", methods=['GET', 'POST'])
def queryByKeyword():
    keyword = request.args.get('keyword')
    sql = "select * from Vibo where keyword = '{0}'".format(keyword)
    update = "update keyword set keyword='{0}'where name='download'".format(keyword)
    cur.execute(sql)
    row = cur.fetchall()
    data = []
    for i in row:
        one_piece_data = (i[1], i[2], i[3], i[4], i[5], i[6],
                          i[7], i[8], i[9], i[10], i[11], i[12])
        column_name = ('user_id', 'created_at_time', 'title_address', 'title_content', 'phone_source', 'forward_count',
                       'comment_count', 'attitudes_count', 'user_screen_name', 'user_type', 'scary_date', 'keyword')
        data.append(dict(zip(column_name, one_piece_data)))
    cur.execute(update)
    conn.commit()
    print(data)
    return {
        "data": data
    }


@app.route('/facetemp')
def reset():
    sql = "select tip from cmd where name='startRequest'"
    sql_keyword = "select state from textTemp where name='keyword'"
    getTime = "select state from textTemp where name='time'"
    cur.execute(sql)
    row = cur.fetchall()
    cur.execute(sql_keyword)
    fow = cur.fetchall()
    cur.execute(getTime)
    tow = cur.fetchall()
    timeStart = float(tow[0][0])
    Time = time.time()-timeStart
    hour = time.strftime("%H", time.localtime(Time))
    minute = time.strftime("%M", time.localtime(Time))
    second = time.strftime("%S", time.localtime(Time))
    if row[0][0] == 1:
        return {
            "isOk": True,
            "keyword":fow[0][0],
            "hour":hour,
            "minute":minute,
            "second":second
        }
    else:
        return {
            "isOk":False,
            "keyword":""
        }


@app.route('/down')
def down():
    sql = "select keyword from keyword where name='download'"
    cur.execute(sql)
    row = cur.fetchall()
    file_name = row[0][0]
    sql = "select * from Vibo where keyword = '{0}'".format(file_name)
    cur.execute(sql)
    row = cur.fetchall()
    data = []
    for i in row:
        one_piece_data = (i[12], i[11], i[3], i[9], i[2], i[4],
                          i[1], i[10], i[5], i[8], i[7], i[6])
        column_name_down = ('关键词', '爬取日期', '微博地址', '用户昵称', '发文日期', '文章内容',
                            '文章内容用户ID', '认证类型', '手机类型', '点赞数','评论数', '转发数')
        data.append(dict(zip(column_name_down, one_piece_data)))
    df = pd.DataFrame(data)
    df.to_excel('./download/{}.xlsx'.format(file_name), index=False)
    UPLOAD_FOLDER = './download/{}.xlsx'.format(file_name)
    ROOT_FOLDER = os.path.join(os.getcwd(), UPLOAD_FOLDER)  # 整合绝对路径
    response = make_response(send_file(ROOT_FOLDER, mimetype='xlsx/csv', attachment_filename='Adjacency.csv'))
    return response


if __name__ == '__main__':
    app.run()
    """--host=192.168.43.253 --port=5000"""

